/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package edu.soc.cs5226.gui.entities;
import edu.soc.cs5226.gui.entities.util.Constants;
import java.util.ArrayList;
import java.util.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import java.sql.Timestamp;

/**
 *
 * @author Huimin and Huijia
 */
public class DAO{
    
    public List<DBParameter> getspsList(Timestamp now)throws SQLException, NamingException{
        
        List<DBParameter> sharedpoolList = new ArrayList<DBParameter>();
        
        //Retrieve Data from Database
        Context ctx = new InitialContext();
        DataSource ds = (DataSource) ctx.lookup(Constants.JAVADB);
        Connection conn = ds.getConnection();
        //Timestamp now = new Timestamp(TimeUtil.getInstance().getTime());
        Timestamp prevDay = new Timestamp(now.getTime() - (24*60*60*1000));
        
        try {
            PreparedStatement pstmt = conn.prepareStatement ("select dateTime, pinHits, pins, hitRatio, "
                    + "sps_healthStatus from statistics where dateTime between ? and ? order by dateTime");
            pstmt.setTimestamp(1, prevDay);
            pstmt.setTimestamp(2, now);
            
            ResultSet rs = pstmt.executeQuery();
            
            while (rs.next()) {
                SharedPool sps_object = new SharedPool();
                sps_object.setdateTime(rs.getTimestamp("dateTime"));
                sps_object.setpinHits(rs.getLong("pinHits"));
                sps_object.setpins(rs.getLong("pins"));
                sps_object.sethitRatio(rs.getLong("hitRatio"));
                sps_object.setsps_healthStatus(rs.getString("sps_healthStatus"));
                
                sharedpoolList.add(sps_object);
            } // while
        }
        catch (SQLException se)
        {
            System.out.println("SQL exception in retrieving sharedpoollist");
            se.printStackTrace();
        }
        finally {
            conn.close();
            return sharedpoolList;
        }
    } // getspsList
    
    //--------------------------------------------------------------------------------
    
    public List<DBParameter> getbcList(Timestamp now)throws SQLException, NamingException{
        
        List<DBParameter> buffercacheList = new ArrayList<DBParameter>();
        
        //Retrieve Data from Database
        Context ctx = new InitialContext();
        DataSource ds = (DataSource) ctx.lookup(Constants.JAVADB);
        Connection conn = ds.getConnection();
        //Timestamp now = new Timestamp(TimeUtil.getInstance().getTime());
        Timestamp prevDay = new Timestamp(now.getTime() - (24*60*60*1000));
        
        try {
            PreparedStatement pstmt = conn.prepareStatement ("select dateTime, dbBlockGets, consistentGets, "
                    + "physicalReads, cacheHitRatio, bc_healthStatus from statistics where dateTime "
                    + "between ? and ? order by dateTime");
            pstmt.setTimestamp(1, prevDay);
            pstmt.setTimestamp(2, now);
            
            ResultSet rs = pstmt.executeQuery();
            
            while (rs.next()) {
                BufferCache bc_object = new BufferCache();
                bc_object.setdateTime(rs.getTimestamp("dateTime"));
                bc_object.setConsistentGets(rs.getLong("consistentGets"));
                bc_object.setDbBlockGets(rs.getLong("dbBlockGets"));
                bc_object.setPhysicalReads(rs.getLong("physicalReads"));
                bc_object.setCacheHitRatio(rs.getLong("cacheHitRatio"));
                bc_object.setBc_healthStatus(rs.getString("bc_healthStatus"));
                
                buffercacheList.add(bc_object);
            } // while
        }
        catch (SQLException se)
        {
            System.out.println("SQL exception in retrieving buffercachelist");
            se.printStackTrace();
        }
        finally {
            conn.close();
            return buffercacheList;
        }
    } // getbcList
    
    //--------------------------------------------------------------------------------
    
    public List<DBParameter> getrlbList(Timestamp now)throws SQLException, NamingException{
        
        List<DBParameter> redologbufList = new ArrayList<DBParameter>();
        
        //Retrieve Data from Database
        Context ctx = new InitialContext();
        DataSource ds = (DataSource) ctx.lookup(Constants.JAVADB);
        Connection conn = ds.getConnection();
        //Timestamp now = new Timestamp(TimeUtil.getInstance().getTime());
        Timestamp prevDay = new Timestamp(now.getTime() - (24*60*60*1000));
        
        try {
            PreparedStatement pstmt = conn.prepareStatement ("select dateTime, redoLogSpaceRequest, "
                    + "lb_healthStatus from statistics where dateTime "
                    + "between ? and ? order by dateTime");
            pstmt.setTimestamp(1, prevDay);
            pstmt.setTimestamp(2, now);
            
            ResultSet rs = pstmt.executeQuery();
            
            while (rs.next()) {
                RedoLogBuffer rlb_object = new RedoLogBuffer();
                rlb_object.setdateTime(rs.getTimestamp("dateTime"));
                rlb_object.setRedoLogSpaceRequest(rs.getLong("redoLogSpaceRequest"));
                rlb_object.setLb_healthStatus(rs.getString("lb_healthStatus"));
                
                redologbufList.add(rlb_object);
            } // while
        }
        catch (SQLException se)
        {
            System.out.println("SQL exception in retrieving redologbufList");
            se.printStackTrace();
        }
        finally {
            conn.close();
            return redologbufList;
        }
    } // getrlbList
    
    //--------------------------------------------------------------------------------
    
    public List<DBParameter> getsmList(Timestamp now)throws SQLException, NamingException{
        
        List<DBParameter> sortmemoryList = new ArrayList<DBParameter>();
        
        //Retrieve Data from Database
        Context ctx = new InitialContext();
        DataSource ds = (DataSource) ctx.lookup(Constants.JAVADB);
        Connection conn = ds.getConnection();
        //Timestamp now = new Timestamp(TimeUtil.getInstance().getTime());
        Timestamp prevDay = new Timestamp(now.getTime() - (24*60*60*1000));
        
        try {
            PreparedStatement pstmt = conn.prepareStatement ("select dateTime, sortsMemory, "
                    + "sortsDisk, sortingToDisk, s_healthStatus from statistics where dateTime "
                    + "between ? and ? order by dateTime");
            pstmt.setTimestamp(1, prevDay);
            pstmt.setTimestamp(2, now);
            
            ResultSet rs = pstmt.executeQuery();
            
            while (rs.next()) {
                SortMemory sm_object = new SortMemory();
                sm_object.setdateTime(rs.getTimestamp("dateTime"));
                sm_object.setSortsMemory(rs.getLong("sortsMemory"));
                sm_object.setSortsDisk(rs.getLong("sortsDisk"));
                sm_object.setSortingToDisk(rs.getLong("sortingToDisk"));
                sm_object.setS_healthStatus(rs.getString("s_healthStatus"));
                
                sortmemoryList.add(sm_object);
            } // while
        }
        catch (SQLException se)
        {
            System.out.println("SQL exception in retrieving sortmemoryList");
            se.printStackTrace();
        }
        finally {
            conn.close();
            return sortmemoryList;
        }
    } // getsmList
    
    //--------------------------------------------------------------------------------
    
    public List<DBParameter> getrsList(Timestamp now)throws SQLException, NamingException{
        
        List<DBParameter> rollbacksegmentList = new ArrayList<DBParameter>();
        
        //Retrieve Data from Database
        Context ctx = new InitialContext();
        DataSource ds = (DataSource) ctx.lookup(Constants.JAVADB);
        Connection conn = ds.getConnection();
        //Timestamp now = new Timestamp(TimeUtil.getInstance().getTime());
        Timestamp prevDay = new Timestamp(now.getTime() - (24*60*60*1000));
        
        try {
            PreparedStatement pstmt = conn.prepareStatement ("select dateTime, waits, "
                    + "gets, rollbackSegment, rs_healthStatus from statistics where dateTime "
                    + "between ? and ? order by dateTime");
            pstmt.setTimestamp(1, prevDay);
            pstmt.setTimestamp(2, now);
            
            ResultSet rs = pstmt.executeQuery();
            
            while (rs.next()) {
                RollbackSegment rs_object = new RollbackSegment();
                rs_object.setdateTime(rs.getTimestamp("dateTime"));
                rs_object.setWaits(rs.getLong("waits"));
                rs_object.setGets(rs.getLong("gets"));
                rs_object.setRollbackSegment(rs.getLong("rollbackSegment"));
                rs_object.setRs_healthStatus(rs.getString("rs_healthStatus"));
                
                rollbacksegmentList.add(rs_object);
            } // while
        }
        catch (SQLException se)
        {
            System.out.println("SQL exception in retrieving rollbacksegmentList");
            se.printStackTrace();
        }
        finally {
            conn.close();
            return rollbacksegmentList;
        }
    } // getrsList
    
    //--------------------------------------------------------------------------------
    
    public TimeBlock getTimeBlock()throws SQLException, NamingException{
        
        TimeBlock defaultBlock = null;
        
        //Retrieve Data from Database
        Context ctx = new InitialContext();
        DataSource ds = (DataSource) ctx.lookup(Constants.JAVADB);
        Connection conn = ds.getConnection();
        
        try {
            PreparedStatement pstmt = conn.prepareStatement ("select xValue, yValue from configuration");
                        
            ResultSet rs = pstmt.executeQuery();
            
            if (rs.next()) {
                defaultBlock = new TimeBlock();
                defaultBlock.setXBlock(rs.getInt("xValue"));
                defaultBlock.setYBlock(rs.getInt("yValue"));
            } 
        }
        catch (SQLException se)
        {
            System.out.println("SQL exception in retrieving x and y timeblock");
            se.printStackTrace();
        }
        finally {
            conn.close();
            return defaultBlock;        
        }        
    } // getTimeBlock
    
    //--------------------------------------------------------------------------------
    
    public HealthStatus getSPSHealthStatus()throws SQLException, NamingException{
        
        HealthStatus defaultStatus = null;
        
        //Retrieve Data from Database
        Context ctx = new InitialContext();
        DataSource ds = (DataSource) ctx.lookup(Constants.JAVADB);
        Connection conn = ds.getConnection();
        
        try {
            PreparedStatement pstmt = conn.prepareStatement ("select xValue, yValue, sps_yellowThreshold, "
                    + "sps_redThreshold from configuration");
                        
            ResultSet rs = pstmt.executeQuery();
            
            if (rs.next()) {
                defaultStatus = new HealthStatus();
                defaultStatus.setXBlock(rs.getInt("xValue"));
                defaultStatus.setYBlock(rs.getInt("yValue"));
                defaultStatus.setyellowThres(rs.getLong("sps_yellowThreshold"));
                defaultStatus.setredThres(rs.getLong("sps_redThreshold"));
            } 
        }
        catch (SQLException se)
        {
            System.out.println("SQL exception in retrieving yellow and red thresholds for shared pool");
            se.printStackTrace();
        }
        finally {
            conn.close();
            return defaultStatus;  
        }         
    } // getSPSHealthStatus
    
    //--------------------------------------------------------------------------------
    
    public HealthStatus getBCHealthStatus()throws SQLException, NamingException{
        
        HealthStatus defaultStatus = null;
        
        //Retrieve Data from Database
        Context ctx = new InitialContext();
        DataSource ds = (DataSource) ctx.lookup(Constants.JAVADB);
        Connection conn = ds.getConnection();
        
        try {
            PreparedStatement pstmt = conn.prepareStatement ("select xValue, yValue, bc_redThreshold, "
                    + "bc_yellowThreshold from configuration");
                        
            ResultSet rs = pstmt.executeQuery();
            
            if (rs.next()) {
                defaultStatus = new HealthStatus();
                defaultStatus.setXBlock(rs.getInt("xValue"));
                defaultStatus.setYBlock(rs.getInt("yValue"));
                defaultStatus.setyellowThres(rs.getLong("bc_yellowThreshold"));
                defaultStatus.setredThres(rs.getLong("bc_redThreshold"));
            } 
        }
        catch (SQLException se)
        {
            System.out.println("SQL exception in retrieving yellow and red thresholds for buffercache");
            se.printStackTrace();
        }
        finally {
            conn.close();
            return defaultStatus;  
        }         
    } // getBCHealthStatus
    
    //--------------------------------------------------------------------------------
    
    public HealthStatus getRLBHealthStatus()throws SQLException, NamingException{
        
        HealthStatus defaultStatus = null;
        
        //Retrieve Data from Database
        Context ctx = new InitialContext();
        DataSource ds = (DataSource) ctx.lookup(Constants.JAVADB);
        Connection conn = ds.getConnection();
        
        try {
            PreparedStatement pstmt = conn.prepareStatement ("select xValue, yValue, lb_redThreshold, "
                    + "lb_yellowThreshold from configuration");
                        
            ResultSet rs = pstmt.executeQuery();
            
            if (rs.next()) {
                defaultStatus = new HealthStatus();
                defaultStatus.setXBlock(rs.getInt("xValue"));
                defaultStatus.setYBlock(rs.getInt("yValue"));
                defaultStatus.setyellowThres(rs.getLong("lb_yellowThreshold"));
                defaultStatus.setredThres(rs.getLong("lb_redThreshold"));
            } 
        }
        catch (SQLException se)
        {
            System.out.println("SQL exception in retrieving yellow and red thresholds for redologbuffer");
            se.printStackTrace();
        }
        finally {
            conn.close();
            return defaultStatus;  
        }         
    } // getRLBHealthStatus
    
    //--------------------------------------------------------------------------------
    
    public HealthStatus getSMHealthStatus()throws SQLException, NamingException{
        
        HealthStatus defaultStatus = null;
        
        //Retrieve Data from Database
        Context ctx = new InitialContext();
        DataSource ds = (DataSource) ctx.lookup(Constants.JAVADB);
        Connection conn = ds.getConnection();
        
        try {
            PreparedStatement pstmt = conn.prepareStatement ("select xValue, yValue, s_redThreshold, "
                    + "s_yellowThreshold from configuration");
                        
            ResultSet rs = pstmt.executeQuery();
            
            if (rs.next()) {
                defaultStatus = new HealthStatus();
                defaultStatus.setXBlock(rs.getInt("xValue"));
                defaultStatus.setYBlock(rs.getInt("yValue"));
                defaultStatus.setyellowThres(rs.getLong("s_yellowThreshold"));
                defaultStatus.setredThres(rs.getLong("s_redThreshold"));
            } 
        }
        catch (SQLException se)
        {
            System.out.println("SQL exception in retrieving yellow and red thresholds for sortmemory");
            se.printStackTrace();
        }
        finally {
            conn.close();
            return defaultStatus;  
        }         
    } // getSMHealthStatus
    
    //--------------------------------------------------------------------------------
    
    public HealthStatus getRSHealthStatus()throws SQLException, NamingException{
        
        HealthStatus defaultStatus = null;
        
        //Retrieve Data from Database
        Context ctx = new InitialContext();
        DataSource ds = (DataSource) ctx.lookup(Constants.JAVADB);
        Connection conn = ds.getConnection();
        
        try {
            PreparedStatement pstmt = conn.prepareStatement ("select xValue, yValue, rs_redThreshold, "
                    + "rs_yellowThreshold from configuration");
                        
            ResultSet rs = pstmt.executeQuery();
            
            if (rs.next()) {
                defaultStatus = new HealthStatus();
                defaultStatus.setXBlock(rs.getInt("xValue"));
                defaultStatus.setYBlock(rs.getInt("yValue"));
                defaultStatus.setyellowThres(rs.getLong("rs_yellowThreshold"));
                defaultStatus.setredThres(rs.getLong("rs_redThreshold"));
            } 
        }
        catch (SQLException se)
        {
            System.out.println("SQL exception in retrieving yellow and red thresholds for rollbacksegment");
            se.printStackTrace();
        }
        finally {
            conn.close();
            return defaultStatus;  
        }         
    } // getRSHealthStatus
    
    //--------------------------------------------------------------------------------
    
    public long getCurrSortingToDisk()throws SQLException, NamingException{
        
        long currValue = 0;
        
        //Retrieve Data from Database
        Context ctx = new InitialContext();
        DataSource ds = (DataSource) ctx.lookup(Constants.JAVADB);
        Connection conn = ds.getConnection();
        
        try {
            PreparedStatement pstmt = conn.prepareStatement ("select sortingToDisk "
                    + "from statistics where rownum <=1 order by datetime desc");
            
            ResultSet rs = pstmt.executeQuery();
            
            if (rs.next()) {
                currValue = rs.getLong("sortingToDisk");
            } // if
        }
        catch (SQLException se)
        {
            System.out.println("SQL exception in retrieving sortingToDisk");
            se.printStackTrace();
        }
        finally {
            conn.close();
            return currValue;
        }
    } // getCurrSortingToDisk
    
    //--------------------------------------------------------------------------------
    
    public long getCurrCacheHitRatio()throws SQLException, NamingException{
        
        long currValue = 0;
        
        //Retrieve Data from Database
        Context ctx = new InitialContext();
        DataSource ds = (DataSource) ctx.lookup(Constants.JAVADB);
        Connection conn = ds.getConnection();
        
        try {
            PreparedStatement pstmt = conn.prepareStatement ("select cacheHitRatio "
                    + "from statistics where rownum <=1 order by datetime desc");
            
            ResultSet rs = pstmt.executeQuery();
            
            if (rs.next()) {
                currValue = rs.getLong("cacheHitRatio");
            } // if
        }
        catch (SQLException se)
        {
            System.out.println("SQL exception in retrieving cacheHitRatio");
            se.printStackTrace();
        }
        finally {
            conn.close();
            return currValue;
        }
    } // getCurrCacheHitRatio
    
    //--------------------------------------------------------------------------------
    
    public long getCurrRedoLogSpaceRequest ()throws SQLException, NamingException{
        
        long currValue = 0;
        
        //Retrieve Data from Database
        Context ctx = new InitialContext();
        DataSource ds = (DataSource) ctx.lookup(Constants.JAVADB);
        Connection conn = ds.getConnection();
        
        try {
            PreparedStatement pstmt = conn.prepareStatement ("select redoLogSpaceRequest "
                    + "from statistics where rownum <=1 order by datetime desc");
            
            ResultSet rs = pstmt.executeQuery();
            
            if (rs.next()) {
                currValue = rs.getLong("redoLogSpaceRequest");
            } // if
        }
        catch (SQLException se)
        {
            System.out.println("SQL exception in retrieving redoLogSpaceRequest");
            se.printStackTrace();
        }
        finally {
            conn.close();
            return currValue;
        }
    } // getCurrRedoLogSpaceRequest
    
    //--------------------------------------------------------------------------------
    
    public long getCurrRollbackSegment ()throws SQLException, NamingException{
        
        long currValue = 0;
        
        //Retrieve Data from Database
        Context ctx = new InitialContext();
        DataSource ds = (DataSource) ctx.lookup(Constants.JAVADB);
        Connection conn = ds.getConnection();
        
        try {
            PreparedStatement pstmt = conn.prepareStatement ("select rollbackSegment "
                    + "from statistics where rownum <=1 order by datetime desc");
            
            ResultSet rs = pstmt.executeQuery();
            
            if (rs.next()) {
                currValue = rs.getLong("rollbackSegment");
            } // if
        }
        catch (SQLException se)
        {
            System.out.println("SQL exception in retrieving rollbackSegment");
            se.printStackTrace();
        }
        finally {
            conn.close();
            return currValue;
        }
    } // getCurrRollbackSegment
    
    //--------------------------------------------------------------------------------
    
    public long getCurrHitRatio ()throws SQLException, NamingException{
        
        long currValue = 0;
        
        //Retrieve Data from Database
        Context ctx = new InitialContext();
        DataSource ds = (DataSource) ctx.lookup(Constants.JAVADB);
        Connection conn = ds.getConnection();
        
        try {
            PreparedStatement pstmt = conn.prepareStatement ("select hitRatio "
                    + "from statistics where rownum <=1 order by datetime desc");
            
            ResultSet rs = pstmt.executeQuery();
            
            if (rs.next()) {
                currValue = rs.getLong("hitRatio");
            } // if
        }
        catch (SQLException se)
        {
            System.out.println("SQL exception in retrieving hitRatio");
            se.printStackTrace();
        }
        finally {
            conn.close();
            return currValue;
        }
    } // getCurrHitRatio
    
    //--------------------------------------------------------------------------------
    
    public InitParameter getInitParam (String parameter)throws SQLException, NamingException{
        
        InitParameter ipObj = new InitParameter();
        
        //Retrieve Data from Database
        Context ctx = new InitialContext();
        DataSource ds = (DataSource) ctx.lookup(Constants.JAVADB);
        Connection conn = ds.getConnection();
        
        try {
            PreparedStatement pstmt = conn.prepareStatement ("select name, value from v$parameter "
                    + "where name = ?");
            
            pstmt.setString(1, parameter);
            ResultSet rs = pstmt.executeQuery();
            
            if (rs.next()) {
                ipObj.setName(rs.getString("name"));
                ipObj.setValue(rs.getLong("value"));
            } // if
        }
        catch (SQLException se)
        {
            System.out.println("SQL exception in retrieving init.ora parameter");
            se.printStackTrace();
        }
        finally {
            conn.close();
            return ipObj;
        }
    } // getInitParam
    
    //--------------------------------------------------------------------------------
    
    public InitParameter getInitParamRollback (String parameter)throws SQLException, NamingException{
        
        InitParameter ipObj = new InitParameter();
        
        //Retrieve Data from Database
        Context ctx = new InitialContext();
        DataSource ds = (DataSource) ctx.lookup(Constants.JAVADB);
        Connection conn = ds.getConnection();
        
        try {
            PreparedStatement pstmt = conn.prepareStatement ("select name, value from v$parameter "
                    + "where name = ?");
            
            pstmt.setString(1, parameter);
            ResultSet rs = pstmt.executeQuery();
            
            if (rs.next()) {
                ipObj.setName(rs.getString("name"));
                ipObj.setValue(rs.getString("value"));
            } // if
        }
        catch (SQLException se)
        {
            System.out.println("SQL exception in retrieving init.ora parameter");
            se.printStackTrace();
        }
        finally {
            conn.close();
            return ipObj;
        }
    } // getInitParamRollback
    
    //--------------------------------------------------------------------------------
    
    public List<InitParameter> getInitParam2 (String parameter1, String parameter2)throws SQLException, NamingException{
        
        List<InitParameter> ipObjList = new ArrayList<InitParameter>();
        
        //Retrieve Data from Database
        Context ctx = new InitialContext();
        DataSource ds = (DataSource) ctx.lookup(Constants.JAVADB);
        Connection conn = ds.getConnection();
        
        try {
            PreparedStatement pstmt = conn.prepareStatement ("select name, value from v$parameter "
                    + "where name in (?,?)");
            
            pstmt.setString(1,parameter1);
            pstmt.setString(2,parameter2);
            ResultSet rs = pstmt.executeQuery();
            
            while (rs.next()) {
                InitParameter ipObj = new InitParameter();
                ipObj.setName(rs.getString("name"));
                ipObj.setValue(rs.getLong("value"));
                ipObjList.add(ipObj);
            } // while
        }
        catch (SQLException se)
        {
            System.out.println("SQL exception in retrieving init.ora parameter");
            se.printStackTrace();
        }
        finally {
            conn.close();
            return ipObjList;
        }
    } // getInitParam2
}
